package hotel.consultas;

import ifpb.banco.entity.Cliente;
import ifpb.banco.entity.Servico;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.PersistenceException;
import javax.persistence.Query;

/**
 *
 * @author Vanderlan
 */
public class ConsultasHQL {

    private EntityManager em;

    public ConsultasHQL() {
        
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("Projeto-Banco-dados-IPU");
        em = factory.createEntityManager();
      
    }
    //OK
    public void consulta1() {
        System.out.println("1) quais os serviços mais solicitados?");

        String hql ="FROM Servico s WHERE size(s.servicoSolicitadoCollection) >= ALL "
                + "(SELECT size(s2.servicoSolicitadoCollection)  "
                + "FROM ServicoSolicitado s2 GROUP BY s2.id)";
                    
             Query createQuery = em.createQuery(hql);
           
             try{
                 
             List<Servico> servicos = createQuery.getResultList();
             
             for (Servico servico: servicos) {
                    
                System.out.println("Serviço: "+servico.getDescricao());
	  }
       }catch(PersistenceException e){
           
           e.printStackTrace();
       }
            
         
    }
    public void consulta3() {
        
        System.out.println("1- Qual o cliente que frequenta mais?");

        try {
            String hql = "FROM Cliente c WHERE c.hospedagemCollection.size >= ALL (SELECT size(c2.hospedagemCollection)"
                    + " FROM Cliente c2 GROUP BY c2.id)";
           
            
             Query createQuery = em.createQuery(hql);

             Cliente cliente = (Cliente) createQuery.getSingleResult();
            
             System.out.println(cliente.getNome());
             
        } catch (PersistenceException e) {
            e.printStackTrace();
        }
    }
     
    //OK
    public void consulta5() {
        
        System.out.println("1- qual a média salarial dos funcionários por sexo?");

        try {
            String hql = "SELECT f.sexo, AVG(f.salario) FROM Funcionario f GROUP BY f.sexo";
                    
             Query createQuery = em.createQuery(hql);
             
             List list = createQuery.getResultList();
             
             for (int i = 0; i < list.size(); i++) {
                    Object[] valores = (Object[]) list.get(i);
                    String sexo = (String) valores[0];
                    Double media = (Double) valores[1];
                    System.out.println("Sexo: " +sexo + "- Media: "+ media);
	  }
            
        } catch (PersistenceException e) {
            e.printStackTrace();
        }
    }
    //OK
    public void consulta10() {
        
        System.out.println("10 - Quais andares possuem mais clientes hospedados?");

        try {
            String hql = "SELECT ap.andar,  COUNT(*)  FROM Apartamento ap, Hospedagem h "
                    + "WHERE ap.id = h.codApartamento.id " +
                    "AND ap.status = 'O' AND h.dataSaida IS null GROUP BY ap.andar ORDER BY COUNT(*) DESC";
                    
             Query createQuery = em.createQuery(hql);
             
             List list = createQuery.getResultList();
             
             for (int i = 0; i < list.size(); i++) {
                    Object[] valores = (Object[]) list.get(i);
                    Integer andar = (Integer) valores[0];
                    Long hospedes = (Long) valores[1];
                    System.out.printf("Andar: " +andar + " - Hospedes: "+ hospedes);
	  }
            
        } catch (PersistenceException e) {
            e.printStackTrace();
        }
    }
    //OK
    public void consulta11(String tipoQuarto) {
        
        System.out.println("10 - Quais andares possuem mais clientes hospedados?");

        try {
            String hql = "SELECT DISTINCT s FROM Servico s, Hospedagem h, ServicoSolicitado ss, "
                    + "Apartamento a WHERE s.id = ss.codServico.id " +
                      "AND ss.codHospedagem.id = s.id AND h.codApartamento.id = a.id AND a.tipo = '"+tipoQuarto+"'";
                    
             Query createQuery = em.createQuery(hql);
             
             List<Servico> list = createQuery.getResultList();
             
             for(Servico ser: list){
                 
                 System.out.println(ser.getDescricao());
	  }
            
        } catch (PersistenceException e) {
            e.printStackTrace();
        }
    }


}
